﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace CommonLibrary.Models
{
    public class DbHelper
    {
        public static DataRow GetWorkFlow(CommonDatabase db, int id)
        {
            string cmd =
@"SELECT id AS ""Id"", forminstanceid AS ""FormInstanceId"", 
         name AS ""Name"", description AS ""Description"", 
         state AS ""State"" 
FROM wf_Workflow WHERE id = @id";
            return db.SetCommand(cmd, db.Parameter("@id", id))
                     .ExecuteDataTable().Rows[0];
        }

        public static int InsertTaskObject(
            CommonDatabase db,
            int taskId,
            int flowObjectId,
            int workUserId,
            int stepId,
            string taskName)
        {
            string cmd =
@"INSERT INTO wf_taskobject(taskid, flowobjectid, workuserid, taskname, stepid)
VALUES (@taskid, @flowobjectid, @workuserid, @taskname, @stepid)
RETURNING id;";

            return db.SetCommand(
                        cmd,
                        db.Parameter("@taskid", taskId),
                        db.Parameter("@flowobjectid", flowObjectId),
                        db.Parameter("@workuserid", workUserId),
                        db.Parameter("@taskname", taskName),
                        db.Parameter("@stepid", stepId))
                     .ExecuteScalar<int>();
        }


        public static List<int> QueryUsersByOrgRole(
            CommonDatabase db,
            int org,
            int role)
        {
            string cmd =
@"select UserId from sys_UserOrgPosition where OrgId = @org
intersect 
select UserId from sys_UserRole where RoleId = @role";
            return db.SetCommand(
                        cmd,
                        db.Parameter("@org", org),
                        db.Parameter("@role", role))
                .ExecuteScalarList<int>();
        }

        public static List<int> QueryUsersByRole(CommonDatabase db, int role)
        {
            string cmd = @"select UserId from sys_UserRole where roleid = @role";
            return db.SetCommand(cmd, db.Parameter("@role", role))
                .ExecuteScalarList<int>();
        }

        public static int? GetParentUser(CommonDatabase db, int user, int org)
        {
            return db.sys_UserOrgPosition.Where(k => k.UserId == user && k.OrgId == org)
                     .Select(k => k.ParentUserId)
                     .FirstOrDefault();
        }

        public static DataRow GetTaskObject(CommonDatabase db, int taskObjectId)
        {
            string cmd = @"select * from wf_taskobject where id = @id";
            return db.SetCommand(cmd, db.Parameter("@id", taskObjectId))
                     .ExecuteDataTable().Rows[0];
        }

        public static DataRow GetFlowObject(CommonDatabase db, int flowObjectId)
        {
            string cmd = @"select * from wf_flowobject where id = @id";
            return db.SetCommand(cmd, db.Parameter("@id", flowObjectId))
                     .ExecuteDataTable().Rows[0];
        }

        public static DataRow GetTask(CommonDatabase db, int task)
        {
            return db.SetCommand(
                                "SELECT * FROM wf_task WHERE id = @id",
                                db.Parameter("@id", task))
                     .ExecuteDataTable().Rows[0];
        }

        public static void UpdateFlowObject(
            CommonDatabase db,
            int flowObjectId,
            int state,
            string description)
        {
            var cmd =
@"UPDATE wf_flowobject
   SET description = @description, state = @state, updatedtime = now()
WHERE id = @id;";
            db.SetCommand(
                cmd,
                db.Parameter("@description", description),
                db.Parameter("@state", state),
                db.Parameter("@id", flowObjectId))
              .ExecuteNonQuery();
        }

        internal static void UpdateTaskObject(
            CommonDatabase db,
            int taskObjectId,
            int state,
            string result)
        {
            var cmd =
@"UPDATE wf_taskobject
   SET result = @result, state = @state, updatedtime = now()
WHERE id = @id;";
            db.SetCommand(
                cmd,
                db.Parameter("@result", result),
                db.Parameter("@state", state),
                db.Parameter("@id", taskObjectId))
              .ExecuteNonQuery();
        }

        public static DataRow GetWorkItem(CommonDatabase db, int taskObjectId)
        {
            var cmd =
@"select * from view_workitem where id = @id";
            return db.SetCommand(cmd, db.Parameter("@id", taskObjectId))
                     .ExecuteDataTable().Rows[0];
        }

        public static DataRow GetViewFlowObject(CommonDatabase db, int flowObjectId)
        {
            var cmd =
@"select * from view_flowobject where id = @id";
            return db.SetCommand(cmd, db.Parameter("@id", flowObjectId))
                     .ExecuteDataTable().Rows[0];
        }

        public static void InsertFlowObjectCC(CommonDatabase db, int flowObjectId, int toUser)
        {
            var cmd =
@"insert into wf_flowobject_cc(flowobjectid, touser) values(@flowObjectId, @toUser);";

            db.SetCommand(
                cmd,
                db.Parameter("@flowObjectId", flowObjectId),
                db.Parameter("@toUser", toUser))
              .ExecuteNonQuery();
        }
    }
}
